<?php
namespace frontend\controllers;

use Yii;
use PDO;
use yii\web\Controller;

class DbsController extends Controller
{
    public $enableCsrfValidation = false;
    /*
     * 禁用yii样式
     */
    public $layout = false;

    /**
     * 公共链接PDO
     */
    public function link_pdo($k){
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $dbh = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
        return $dbh;
    }

    /**
     * 创建数据库
     */
    public function actionShow_db(){
        $k = Yii::$app->session->get('link_k');
        $dbh = $this->link_pdo($k); //调用链接数据库方法
        if(Yii::$app->request->ispost){
            $dbname_u = Yii::$app->request->post('dbname_u'); //数据库名称
            $charset_u = Yii::$app->request->post('charset_u'); //字符集
            $charset_r = Yii::$app->request->post('charset_r'); //排序规则
            $sql = "CREATE DATABASE `$dbname_u` DEFAULT CHARACTER SET `$charset_u` COLLATE `$charset_r`";
            $dbh->exec($sql);
            $sql = "show character set";
            $all_dbname = "show databases";
            //获取数据库所有库名
            $dbnames = $dbh->query($all_dbname)->fetchAll(PDO::FETCH_ASSOC); 
            $char = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);     
            return $this->render('show_db', [
                'dbnames' => $dbnames,
                'char' => $char,
            ]);
        }else{
            $all_dbname = "show databases";
            $sql = "show character set";
            //获取数据库所有库名
            $dbnames = $dbh->query($all_dbname)->fetchAll(PDO::FETCH_ASSOC); 
            $char = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
            // print_r($dbnames);die;
            return $this->render('show_db', [
                'dbnames' => $dbnames,
                'char' => $char,
            ]);
        }
    }

    public function actionDbnamesql()
    {
        $sql = Yii::$app->request->post('sql');
        $k = Yii::$app->session->get('link_k');
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $dbh = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
        $list = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $keys = array_keys($list[0]);
        $str = '<thead><tr>';
        foreach ($keys as $qq => $ww) {
            $str .= '<th>';
            $str .= $ww;
            $str .= '</th>';
        }
        $str .= '</tr></thead><tbody>';
        foreach ($list as $k => $v) {
            $str .= '<tr>';
            foreach ($v as $key => $value) {
                $str .= '<td>';
                $str .= $value;
                $str .= '</td>';
            }
            $str .= '</tr>';
        }
        $str .= '</tbody>';
        return $str;
    }
    public function actionSelectsql()
    {
        $sql = Yii::$app->request->post('sql');
        $k = Yii::$app->session->get('link_k');
        $dbname = Yii::$app->session->get('dbname');
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $dbh = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];dbname=$dbname;charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
        $list = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $keys = array_keys($list[0]);
        $str = '<thead><tr>';
        foreach ($keys as $qq => $ww) {
            $str .= '<th>';
            $str .= $ww;
            $str .= '</th>';
        }
        $str .= '</tr></thead><tbody>';
        foreach ($list as $k => $v) {
            $str .= '<tr>';
            foreach ($v as $key => $value) {
                $str .= '<td>';
                $str .= $value;
                $str .= '</td>';
            }
            $str .= '</tr>';
        }
        $str .= '</tbody>';
        return $str;
    }
    public function actionTablesql()
    {
        $sql = Yii::$app->request->post('sql');
        // $sql = "select * from goods";
        $k = Yii::$app->session->get('link_k');
        $dbname = Yii::$app->session->get('dbname');
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $dbh = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];dbname=$dbname;charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
        $list = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $keys = array_keys($list[0]);
        $str = '<thead><tr>';
        foreach ($keys as $qq => $ww) {
            $str .= '<th>';
            $str .= $ww;
            $str .= '</th>';
        }
        $str .= '</tr></thead><tbody>';
        foreach ($list as $k => $v) {
            $str .= '<tr>';
            foreach ($v as $key => $value) {
                $str .= '<td>';
                $str .= $value;
                $str .= '</td>';
            }
            $str .= '</tr>';
        }
        $str .= '</tbody>';
        return $str;
    }
    /**
     * 展示当前链接的所有数据库
     */
    public function actionShows_db(){
        $k = Yii::$app->session->get('link_k');
        $dbh = $this->link_pdo($k);
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        if(count($dbinfos_arr) === 0 || $dbinfos_arr === false){
            $dbinfos_arr = [];
        }
        //获取数据库所有库名
        $all_dbname = "show databases";
        $dbnames = $dbh->query($all_dbname)->fetchAll(PDO::FETCH_ASSOC); 
        if(count($dbnames) === 0 || $dbnames === false){
            $dbnames = [];
        }
        $tables = [];
        return $this->render('show_db', [
            'dbinfos_arr' => $dbinfos_arr,
            'dbnames'     => $dbnames,
            'k'           => $k
        ]);
    }

    /**
     * 删除数据库
     */
    public function actionDel_db(){
        $k = Yii::$app->session->get('link_k');
        $dbh = $this->link_pdo($k); //链接当前链接
        $dbname = Yii::$app->request->post('dbname'); //数据库名称
        $sql = "drop database `$dbname`";
        $deldb = $dbh->exec($sql);
        if($deldb === 0){
            return json_encode('1');die;
        }
    }

    /**
     * 删除链接
     */
    public function actionDel_link(){
        $k = Yii::$app->request->post('k');
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        unset($dbinfos_arr[$k]);
        $dbinfos_arrs = serialize($dbinfos_arr);
        $dbinfos = Yii::$app->session->set('dbinfos', $dbinfos_arrs);
        return json_encode('1');die;
    }
    /**
     * 查询排序规则
     * @return [type] [description]
     */
    public function actionCharset(){
        $char = Yii::$app->request->post('char');
        $k = Yii::$app->session->get('link_k');
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $dbh = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
        $sql = "show collation where Charset='$char'";
        $list = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $str = '';
        foreach ($list as $k => $v) {
            $str .= '<option value="'.$v['Collation'].'">'.$v['Collation'].'</option>';
        }
        return $str;
    }
}